<?php

/**
 * Description of Application_Model_Db
 *
 * @author Jean Brito
 */
class Application_Model_Db {

	static public function CheckDbException(Zend_Db_Exception $error, Application_Model_Base $table) {
		/*
		 * [0] => 42S02
		* [1] => 1146
		* [2] => Table 'agentenet.utilitario_modulo' doesn't exist
		*/
		if ($error->getPrevious()->errorInfo[0] == '42S02') {
			return self::createSqlTable($table);
		}
		/*
		 * [0] => 42S22
		* [1] => 1054
		* [2] => Unknown column 'titulo' in 'field list'
		*/
		if ($error->getPrevious()->errorInfo[0] == '42S22') {
			$field=explode("'",$error->getPrevious()->errorInfo[2]);
			$field=$field[1];
			return self::createSqlAddField($table,$field);
		}
		return false;
	}

	static public function createSqlTable(Application_Model_Base $table) {
		$sql = "create table " . $table->getTable() . " (";
		foreach ($table->getFields() as $field)
			$sql.=self::createField($field) . ',';
		$sql = substr($sql, 0, strlen($sql) - 1) . ")";
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$db->query($sql);
			return true;
		} catch (Zend_Db_Exception $error_string) {
			print $error_string->getCode() . '|' . $error_string->getMessage();
			exit;
			return Application_Model_Db::CheckDbException($error_string, $table);
		}
		return false;
	}

	static public function createSqlAddField(Application_Model_Base $table,$field) {
		$fields=$table->getFields();
		$sql = "alter table " . $table->getTable() . " add (";
		$sql.=self::createField($fields[$field]) . ')';
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$db->query($sql);
			return true;
		} catch (Zend_Db_Exception $error_string) {
			print $error_string->getCode() . '|' . $error_string->getMessage();
			exit;
			return Application_Model_Db::CheckDbException($error_string, $table);
		}
		return false;
	}

	static public function createField(array $field) {
		$fieldclass = 'Application_Model_Fields_' . $field['type'];
		$sqlSetup = $fieldclass::getCreateSql($field);

		$sql = $sqlSetup['name'] . ' ';
		if (isset($sqlSetup['sqlType']))
			$sql.=$sqlSetup['sqlType'];
		if (isset($sqlSetup['sqlSize']))
			$sql.="(" . $sqlSetup['sqlSize'] . ")";
		if (isset($sqlSetup['required']))
			$sql.=" not NULL";
		if (isset($sqlSetup['sqlAI']))
			$sql.=" AUTO_INCREMENT";
		if (isset($sqlSetup['sqlPrimaryKEY']))
			$sql.=", PRIMARY KEY (" . $sqlSetup['name'] . ")";
		return $sql;
	}

	static public function getSqlSelect($model) {
		$select = '';
		foreach ($model->getFields() as $field) {
			if (isset($field['table']))
				$select.= $field['table'] . '.';
			if (isset($field['selectSql']))
				$select.=$field['selectSql'] . ' as ' . $field['name'];
			else
				$select.=$field['name'];
			$select.=',';
		}
		return substr($select, 0, strlen($select) - 1);
	}

	static public function getSqlInsert($model, $data) {
		$sqlFields = '';
		$sqlValues = '';
		foreach ($model->getFields() as $ifield) {
			$field = 'Application_Model_Fields_' . $ifield['type'];
			$field = $field::getInsertFieldSql($ifield, isset($data[$ifield['name']])?$data[$ifield['name']]:'');
			if (!isset($field['readOnly'])) {
				$sqlFields.=',' . $field['name'];
				$sqlValues.=',' . $field['value'];
			}
		}
		return "(" . substr($sqlFields, 1, strlen($sqlFields)) . ")values(" . substr($sqlValues, 1, strlen($sqlValues)) . ")";
	}

	static public function getSqlUpdate($model, $data) {
		$sqlSets = '';
		foreach ($model->getFields() as $ifield) {
			$field = 'Application_Model_Fields_' . $ifield['type'];
			$field = $field::getInsertFieldSql($ifield, $data[$ifield['name']]);
			if (!isset($field['readOnly'])) {
				$sqlSets.=',' . $field['name'].'=' . $field['value'];
			}
		}
		return " set " . substr($sqlSets, 1, strlen($sqlSets));
	}

	static public function getSqlFilter($model, $filter = null){
		if($filter==null)return "";
		$fields=$model->getFields();
		$where = "";
		foreach ($filter as $field=>$value) {
			if($fields[$field]['type']=='password'){
				$where.="and($field=SHA1('$value'))";
			}else{
				$where.="and($field='$value')";
			}
		}
		if($where!='')$where="where".substr($where,3,strlen($where));
		return $where;
	}

	static public function index($model, $filter = null, $start = null, $limit = null, $sort = null) {
		$sql = "select " . self::getSqlSelect($model) . " from " . $model->getTable()." ".self::getSqlfilter($model,$filter);
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$rows = $db->fetchAll($sql);
			return array('success' => true, 'data' => $rows, 'total' => count($rows),'message'=>'Registros lidos com sucesso');
		} catch (Zend_Db_Exception $error_string) {
			Application_Model_Db::CheckDbException($error_string, $model);
			return array('success' => false, 'message' => $error_string->getMessage());
		}
		return array();
	}

	static public function post($model, $data) {
		$sql = "insert into " . $model->getTable() . " " . self::getSqlInsert($model, $data);
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$db->query($sql);
			$id=$db->lastInsertId();

			$data=self::index($model,array('id'=>$id));

			return array('success' => true, 'message' => 'registro inserido com sucesso','data'=>$data['data'][0]);
		} catch (Zend_Db_Exception $error_string) {
			Application_Model_Db::CheckDbException($error_string, $model);
			return array('success' => false, 'message' => $error_string->getMessage());
		}
		return array();
	}

	static public function getSqlPrimaryKEY($model, $id) {
		$where = "";
		foreach ($model->getFields() as $field) {
			$fieldclass = 'Application_Model_Fields_' . $field['type'];
			$sqlSetup = $fieldclass::getCreateSql($field);
			if (isset($sqlSetup['sqlPrimaryKEY'])) {
				$where.="(" . $field['name'] . "=$id)";
			}
		}
		return $where;
	}

	static public function delete($model, $id) {
		$sql = "delete from " . $model->getTable() . " where " . self::getSqlPrimaryKEY($model, $id);
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$db->query($sql);
			return array('success' => true, 'message' => 'registro inserido com sucesso', 'data'=>array());
		} catch (Zend_Db_Exception $error_string) {
			Application_Model_Db::CheckDbException($error_string, $this);
			return array('success' => false, 'message' => $error_string->getMessage());
		}
		return array();
	}

	static public function put($model,$id, $data) {
		$sql = "update " . $model->getTable() . " " . self::getSqlUpdate($model, $data) . " where " . self::getSqlPrimaryKEY($model, $id);
		$db = Zend_Db_Table::getDefaultAdapter();
		try {
			$db->query($sql);
			$data=self::index($model,array('id'=>$id));
			return array('success' => true, 'message' => 'registro atualizado com sucesso' ,'data'=>(isset($data[0])?$data[0]:array()));
		} catch (Zend_Db_Exception $error_string) {
			Application_Model_Db::CheckDbException($error_string, $this);
			return array('success' => false, 'message' => $error_string->getMessage());
		}
		return array();
	}

}
